package Logic;

import java.sql.*;
import java.util.ArrayList;
import java.util.concurrent.Executors;

import Security.PasswordHash;
/**
 * The SQLReader class serves as the link between the User Interface and the database. All required methods for manipulating or requesting information from the database are implemented in this class. See the specific methods from within the class file.
 * Each method opens and closes a connection to the server. You must defined the static variables in this class to properly connect to your database environment!
 *@Throws
 *All errors are thrown to the methods that call methods within this class. In case of commit/rollback, the errors are caught within this class and rethrown to the caller.
 */
public class SQLReader {

	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_URL = "jdbc:mysql://localhost/bankDatabase";

	private static final String USER = "root";
	private static final String PASS = "password";

	private Connection sqlconnection = null;

/**
 * Attemtps to open a connection to the database.
 * Timeout is specified to 4000ms.
 * Sets the autocommit property to false. All transactions must be explicitly committed unless they implicitly commit (such as TRUNCATE).
 * 
 * 
 * @throws SQLException
 * Throws SQL exceptions if connection fails
 */
	public void openConnection() throws SQLException {
		try {
			Class.forName(JDBC_DRIVER).newInstance();
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException e) {
		}

		DriverManager.setLoginTimeout(4);
		sqlconnection = DriverManager.getConnection(DB_URL, USER, PASS);
		sqlconnection.setAutoCommit(false);

	}
/** 
 * Close the SQL connection.
 * if the connections is not open, nothing happens.
 */
	public void closeConnection() {
		try {
			sqlconnection.close();
		} catch (SQLException e) {
			// if connection fails to close, there's nothing we can do anyway
		} catch (NullPointerException e) {
			// connection doesn't exist yet, don't care then
		}
	}
/**
 * Sets the balance of the receiver to his/her amount + the amount specified as a parameter.
 * Uses round math and adds the amount to the receiver balance.
 * Uses round math and subtracts the amount from the senders balance.
 * Inserts the transaction information in the transaction log.
 * If the receiver sender is equal to 1 (bank) the account balance for Id 1 is not changed. 
 * If all transactions are not successful, the rollback method is called on the connection object. Otherwise the commit method is call and the connection is closed.   
 * @param id_receiver
 * The id of the receiver account. 
 * @param id_sender
 * The Id of the sender account.
 * @param amount
 * The Amount being transfered.
 * @param message
 * Message to user in the transactions log.
 * @throws SQLException
 * 
 */
	public void performTransaction(int id_receiver, int id_sender,
			double amount, String message) throws SQLException {

		openConnection();

		PreparedStatement stmt1 = sqlconnection
				.prepareStatement("SELECT balance FROM users WHERE id = ?");
		stmt1.setInt(1, id_receiver);
		ResultSet rs_receiver = stmt1.executeQuery();
		rs_receiver.first();
		double currentBalanceReceiver = rs_receiver.getDouble("balance");
		stmt1.close();

		PreparedStatement stmt2 = sqlconnection
				.prepareStatement("UPDATE users SET balance = ? WHERE id = ?");
		double roundedvalueReceiver = RoundMath.round(
				(currentBalanceReceiver + amount), 2);
		stmt2.setDouble(1, roundedvalueReceiver);
		stmt2.setInt(2, id_receiver);

		PreparedStatement stmt3 = sqlconnection
				.prepareStatement("SELECT balance FROM users WHERE id = ?");
		stmt3.setInt(1, id_sender);
		ResultSet rs_sender = stmt3.executeQuery();
		rs_sender.first();
		double currentBalanceSender = rs_sender.getDouble("balance");
		stmt3.close();

		PreparedStatement stmt4 = sqlconnection
				.prepareStatement("UPDATE users SET balance = ? WHERE id = ?");
		double roundedvalueSender = RoundMath.round(
				(currentBalanceSender - amount), 2);
		stmt4.setDouble(1, roundedvalueSender);
		stmt4.setInt(2, id_sender);

		PreparedStatement stmt5 = sqlconnection
				.prepareStatement("INSERT INTO TRANSACTIONS(ID_SENDER, ID_RECEIVER, AMOUNT, POSTBALANCE_SENDER, POSTBALANCE_RECEIVER, MESSAGE) VALUES(?,?,?,?,?,?)");
		stmt5.setInt(1, id_sender);
		stmt5.setInt(2, id_receiver);
		stmt5.setDouble(3, RoundMath.round(amount, 2));
		if (id_sender != 1) {
			stmt5.setDouble(4, roundedvalueSender);
		} else {
			stmt5.setDouble(4, 0);
		}
		if (id_receiver != 1) {
			stmt5.setDouble(5, roundedvalueReceiver);
		} else {
			stmt5.setDouble(5, 0);
		}
		stmt5.setString(6, message);

		try {
			if (id_receiver != 1) {
				stmt2.executeUpdate();
				stmt2.close();
			}
			if (id_sender != 1) {
				stmt4.executeUpdate();
				stmt4.close();
			}
			stmt5.executeUpdate();
			stmt5.close();
			sqlconnection.commit();
			closeConnection();
		} catch (SQLException e) {
			sqlconnection.rollback();
			closeConnection();
			throw e;
		}

	} // end method editBalance
	
/** 
 * Opens the SQL connection and initially set the return value to -1. 
 * Then checks the email in the database.
 * If the email is found the returnvalue is set to the Emails id. 
 * if the email is not found the id is set to -1 and the user will know that the email does not exist.
 * @param email
 * @return
 * Returns the ID that matches the Email 
 * @throws SQLException
 * Throws SQL if error occurs
 */

	public int getUserID(String email) throws SQLException {
		openConnection();
		int returnvalue = -1;

		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT id FROM users WHERE email=?");
		stmt.setString(1, email);
		ResultSet rs = stmt.executeQuery();
		if (rs.first()) {
		returnvalue = rs.getInt("id");
		}
		stmt.close();
		closeConnection();

		return returnvalue;
	} // end method getUserID

	
	/**
	 * Matches the ID to the database. If the ID is found the balance is returned. 
	 * If the ID is not found the return value is set to -1 and the connection is closed. We can do this as no accounts can have a negative amount.
	 * @param id
	 * The ID of the user to fetch balance for.
	 * @return
	 * The balance of user with ID id.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public double getUpdatedBalance(int id) throws SQLException {
		openConnection();
		double returnvalue = -1;
		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT balance FROM users WHERE id=?");
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		rs.first();
		returnvalue = rs.getDouble("balance");

		stmt.close();
		closeConnection();
		return returnvalue;

	}
/**
 * Returns an object of the type Account based on the ID input and the output from SQL. 
 * 
 * @param id
 * The ID of the Account to return.
 * @return
 * The account object created from the SQL statement.
 * @throws SQLException
 * If error occurs.
 */
	public Account getAccount(int id) throws SQLException {
		openConnection();
		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT email,firstname,lastname,balance,admin,id,deactivated FROM users WHERE id=?");
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		rs.first();
		Account account = new Account(rs.getString("email"), rs.getInt("id"),
				rs.getDouble("balance"), rs.getString("firstname"),
				rs.getString("lastname"), rs.getBoolean("admin"),
				rs.getBoolean("deactivated"));
		stmt.close();
		closeConnection();
		return account;
	}// end method returnaccount
/**
 * Adds an account to SQL based on user input and
 * sets the balance of the new user.
 * Creates a new object in the transaction list where the bank has transfered the initial balance to the user.
 * 
 * @param email
 * the email of the user
 * @param password
 * the password of the user
 * @param balance
 * the balance of the user
 * @param firstname
 * The email of the user
 * @param lastname
 * the lastname of the user
 * @param admin
 * is the user admin yes,no?
 * @throws SQLException
 * Throws SQL if error occurs
 */
	public void addAccount(String email, char[] password, double balance,
			String firstname, String lastname, boolean admin)
			throws SQLException {
		openConnection();
		String hashedpassword = PasswordHash.HashPassword(password);
		double roundedbalance = RoundMath.round(balance, 2);
		PreparedStatement stmt = sqlconnection
				.prepareStatement("INSERT INTO USERS(EMAIL, PASSWORD, BALANCE, FIRSTNAME, LASTNAME, ADMIN) VALUES (?, ?, ?, ?, ?, ?)");
		stmt.setString(1, email);
		stmt.setString(2, hashedpassword);
		stmt.setDouble(3, balance);
		stmt.setString(4, firstname);
		stmt.setString(5, lastname);
		stmt.setBoolean(6, admin);
		stmt.executeUpdate();
		stmt.close();

		PreparedStatement stmt2 = sqlconnection
				.prepareStatement("SELECT id FROM users WHERE email=?");
		stmt2.setString(1, email);
		ResultSet rs = stmt2.executeQuery();
		rs.first();
		int newid = rs.getInt("id");
		stmt2.close();

		PreparedStatement stmt5 = sqlconnection
				.prepareStatement("INSERT INTO TRANSACTIONS(ID_SENDER, ID_RECEIVER, AMOUNT, POSTBALANCE_SENDER, POSTBALANCE_RECEIVER, MESSAGE) VALUES(?,?,?,?,?,?)");
		stmt5.setInt(1, 1);
		stmt5.setInt(2, newid);
		stmt5.setDouble(3, roundedbalance);
		stmt5.setDouble(4, 0);
		stmt5.setDouble(5, roundedbalance);
		stmt5.setString(6, "Initial balance");

		try {
			stmt5.executeUpdate();
			stmt5.close();
			sqlconnection.commit();
			closeConnection();
		} catch (SQLException e) {
			sqlconnection.rollback();
			closeConnection();
			throw e;
		}

	}// end method addAccount
/**
 * Disables the account based on the ID.
 * 
 * @param id
 * The ID of the user to disable.
 * @throws SQLException
 * If any error occurs.
 */
	public void disableAccount(int id) throws SQLException {
		openConnection();
		PreparedStatement stmt = sqlconnection
				.prepareStatement("UPDATE users SET DEACTIVATED=1 WHERE id=?");
		stmt.setInt(1, id);

		try {
			stmt.executeUpdate();
			stmt.close();
			sqlconnection.commit();
			closeConnection();
		} catch (SQLException e) {
			sqlconnection.rollback();
			closeConnection();
			throw e;
		}

	}
	/**
	 * Enables the account based on the ID.
	 * 
	 * @param id
	 * The ID of the user to enable.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public void enableAccount(int id) throws SQLException {
		openConnection();
		PreparedStatement stmt = sqlconnection
				.prepareStatement("UPDATE users SET DEACTIVATED=0 WHERE id=?");
		stmt.setInt(1, id);

		try {
			stmt.executeUpdate();
			stmt.close();
			sqlconnection.commit();
			closeConnection();
		} catch (SQLException e) {
			sqlconnection.rollback();
			closeConnection();
			throw e;
		}

	}
	/**
	 * Updates the first and last name of the account based on ID.
	 * @param id
	 * The ID of the account to update.
	 * @param firstname
	 * The first name to set for the account.
	 * @param lastname
	 * The last name to set for the account.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public void updateAccount(int id, String firstname, String lastname)
			throws SQLException {
		openConnection();
		PreparedStatement stmt = sqlconnection
				.prepareStatement("UPDATE users SET FIRSTNAME=?,LASTNAME=? WHERE id=?");
		stmt.setInt(3, id);
		stmt.setString(1, firstname);
		stmt.setString(2, lastname);

		try {
			stmt.executeUpdate();
			stmt.close();
			sqlconnection.commit();
			closeConnection();
		} catch (SQLException e) {
			sqlconnection.rollback();
			closeConnection();
			throw e;
		}

	}
	/**
	 * Returns an arraylist of 15 accounts or less whose IDs are less than the specified parameter. This makes it possible to append fetched data to an already existing list without creating duplicate entries.
	 * @param fromid
	 * The highest ID to fetch.
	 * @return
	 * The arraylist of accounts matching the criteria.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public ArrayList<Account> getUsers(int fromid) throws SQLException {
		openConnection();
		ArrayList<Account> list = new ArrayList<Account>();
		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT email,firstname,lastname,balance,admin,id,deactivated FROM users where id < ? ORDER BY id DESC LIMIT 16;");
		stmt.setInt(1, fromid);
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			list.add(new Account(rs.getString("email"), rs.getInt("id"), rs
					.getDouble("balance"), rs.getString("firstname"), rs
					.getString("lastname"), rs.getBoolean("admin"), rs
					.getBoolean("deactivated")));
		}

		stmt.close();
		closeConnection();
		return list;
	}// end method returnaccount
	/**
	 * Fetches an arraylist of users based on search parameters (a string). If the string matches either e-mail, firstname or lastname of an account, the account is added to the arraylist.
	 * @param searchargument
	 * The string that must be matched.
	 * @return
	 * The list of users meeting matched criteria.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public ArrayList<Account> getSpecificUsers(String searchargument)
			throws SQLException {
		openConnection();
		ArrayList<Account> results = new ArrayList<Account>();
		
		PreparedStatement stmt = sqlconnection.prepareStatement(("SELECT email,firstname,lastname,balance,admin,id,deactivated FROM users where (email LIKE ? OR firstname LIKE ? OR lastname LIKE ?) order by id desc limit 15;"));
		stmt.setString(1, "%"+searchargument+"%");
		stmt.setString(2, "%"+searchargument+"%");
		stmt.setString(3, "%"+searchargument+"%");
		
		ResultSet rs = stmt.executeQuery();
		
		while (rs.next()) {
			results.add(new Account(rs.getString("email"), rs.getInt("id"), rs
					.getDouble("balance"), rs.getString("firstname"), rs
					.getString("lastname"), rs.getBoolean("admin"), rs
					.getBoolean("deactivated")));
		}
		
		stmt.close();
		closeConnection();
		return results;
	}
	/**
	 * Returns an arraylist of all transactions where either the sender of receiver matches the ID specified.
	 * This statement uses and INNER JOIN'ed UNION'ed statement to correctly fetch data. See the string as prepared statement for details.
	 * @param id
	 * The ID of the user to fetch transactions for.
	 * @return
	 * An arraylist of all transactions for the user.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public ArrayList<Transaction> getTransactions(int id) throws SQLException {
		openConnection();
		ArrayList<Transaction> list = new ArrayList<Transaction>();
		double invertedamount = 0;

		PreparedStatement stmt1 = sqlconnection
				.prepareStatement("SELECT USERS.EMAIL,TRANSACTIONS.AMOUNT,TRANSACTIONS.DATESTAMP,TRANSACTIONS.POSTBALANCE_RECEIVER,TRANSACTIONS.POSTBALANCE_SENDER,TRANSACTIONS.ID_RECEIVER,TRANSACTIONS.ID_SENDER,TRANSACTIONS.MESSAGE,TRANSACTIONS.ID FROM USERS INNER JOIN TRANSACTIONS ON TRANSACTIONS.ID_RECEIVER=USERS.ID WHERE TRANSACTIONS.ID_SENDER=? UNION SELECT USERS.EMAIL,TRANSACTIONS.AMOUNT,TRANSACTIONS.DATESTAMP,TRANSACTIONS.POSTBALANCE_RECEIVER,TRANSACTIONS.POSTBALANCE_SENDER,TRANSACTIONS.ID_RECEIVER,TRANSACTIONS.ID_SENDER,TRANSACTIONS.MESSAGE,TRANSACTIONS.ID FROM USERS INNER JOIN TRANSACTIONS ON TRANSACTIONS.ID_SENDER=USERS.ID WHERE TRANSACTIONS.ID_RECEIVER=? ORDER BY DATESTAMP DESC;");
		stmt1.setInt(1, id);
		stmt1.setInt(2, id);
		ResultSet rs1 = stmt1.executeQuery();

		while (rs1.next()) {
			String datefromDB = rs1.getString("DATESTAMP");
			String simpledate = datefromDB.substring(0,
					Math.min(datefromDB.length(), 10));

			if (rs1.getInt("ID_RECEIVER") == id) {
				if (rs1.getInt("ID_SENDER") == 1) {
					list.add(new Transaction("BCBS Bank", rs1
							.getDouble("AMOUNT"), simpledate, datefromDB, rs1
							.getDouble("POSTBALANCE_RECEIVER"), rs1
							.getString("MESSAGE"), rs1.getInt("ID")));
				} else {
					list.add(new Transaction(rs1.getString("EMAIL"), rs1
							.getDouble("AMOUNT"), simpledate, datefromDB, rs1
							.getDouble("POSTBALANCE_RECEIVER"), rs1
							.getString("MESSAGE"), rs1.getInt("ID")));
				}

			} else {
				if (rs1.getInt("ID_RECEIVER") == 1) {
					invertedamount = RoundMath.round(
							(0 - rs1.getDouble("AMOUNT")), 2);
					list.add(new Transaction("BCBS Bank", invertedamount,
							simpledate, datefromDB, rs1
									.getDouble("POSTBALANCE_SENDER"), rs1
									.getString("MESSAGE"), rs1.getInt("ID")));
				} else {
					invertedamount = RoundMath.round(
							(0 - rs1.getDouble("AMOUNT")), 2);
					list.add(new Transaction(rs1.getString("EMAIL"),
							invertedamount, simpledate, datefromDB, rs1
									.getDouble("POSTBALANCE_SENDER"), rs1
									.getString("MESSAGE"), rs1.getInt("ID")));
				}
			}
		}
		stmt1.close();
		closeConnection();
		return list;
	}// end method returnaccount

	/**
	 * Returns an arraylist of 15 or less transactions where either the sender or receiver ID matches the ID specified and where the ID of the transaction is less than specified. This makes it possible to append data to an existing arraylist without creating duplicate entries or missing data.
	 * This statement uses and INNER JOIN'ed UNION'ed statement to correctly fetch data. See the string as prepared statement for details.
	 * @param id
	 * The ID of the user to fetch transactions for.
	 * @param beforethis
	 * Transactions with an ID of less than this will be fetched.
	 * @return
	 * An arraylist of 15 or less transactions for the user.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public ArrayList<Transaction> getTransactions(int id, int beforethis)
			throws SQLException {
		openConnection();
		ArrayList<Transaction> list = new ArrayList<Transaction>();
		double invertedamount = 0;

		PreparedStatement stmt1 = sqlconnection
				.prepareStatement("SELECT USERS.EMAIL,TRANSACTIONS.AMOUNT,TRANSACTIONS.DATESTAMP,TRANSACTIONS.POSTBALANCE_RECEIVER,TRANSACTIONS.POSTBALANCE_SENDER,TRANSACTIONS.ID_RECEIVER,TRANSACTIONS.ID_SENDER,TRANSACTIONS.MESSAGE,TRANSACTIONS.ID FROM USERS INNER JOIN TRANSACTIONS ON TRANSACTIONS.ID_RECEIVER=USERS.ID WHERE TRANSACTIONS.ID_SENDER=? AND TRANSACTIONS.ID < ? UNION SELECT USERS.EMAIL,TRANSACTIONS.AMOUNT,TRANSACTIONS.DATESTAMP,TRANSACTIONS.POSTBALANCE_RECEIVER,TRANSACTIONS.POSTBALANCE_SENDER,TRANSACTIONS.ID_RECEIVER,TRANSACTIONS.ID_SENDER,TRANSACTIONS.MESSAGE,TRANSACTIONS.ID FROM USERS INNER JOIN TRANSACTIONS ON TRANSACTIONS.ID_SENDER=USERS.ID WHERE TRANSACTIONS.ID_RECEIVER=? AND TRANSACTIONS.ID < ? ORDER BY ID DESC LIMIT 16;");
		stmt1.setInt(1, id);
		stmt1.setInt(2, beforethis);
		stmt1.setInt(3, id);
		stmt1.setInt(4, beforethis);
		ResultSet rs1 = stmt1.executeQuery();

		while (rs1.next()) {

			String datefromDB = rs1.getString("DATESTAMP");
			String simpledate = datefromDB.substring(0,
					Math.min(datefromDB.length(), 10));

			if (rs1.getInt("ID_RECEIVER") == id) {
				if (rs1.getInt("ID_SENDER") == 1) {
					list.add(new Transaction("BCBS Bank", rs1
							.getDouble("AMOUNT"), simpledate, datefromDB, rs1
							.getDouble("POSTBALANCE_RECEIVER"), rs1
							.getString("MESSAGE"), rs1.getInt("ID")));
				} else {
					list.add(new Transaction(rs1.getString("EMAIL"), rs1
							.getDouble("AMOUNT"), simpledate, datefromDB, rs1
							.getDouble("POSTBALANCE_RECEIVER"), rs1
							.getString("MESSAGE"), rs1.getInt("ID")));
				}

			} else {
				if (rs1.getInt("ID_RECEIVER") == 1) {
					invertedamount = RoundMath.round(
							(0 - rs1.getDouble("AMOUNT")), 2);
					list.add(new Transaction("BCBS Bank", invertedamount,
							simpledate, datefromDB, rs1
									.getDouble("POSTBALANCE_SENDER"), rs1
									.getString("MESSAGE"), rs1.getInt("ID")));
				} else {
					invertedamount = RoundMath.round(
							(0 - rs1.getDouble("AMOUNT")), 2);
					list.add(new Transaction(rs1.getString("EMAIL"),
							invertedamount, simpledate, datefromDB, rs1
									.getDouble("POSTBALANCE_SENDER"), rs1
									.getString("MESSAGE"), rs1.getInt("ID")));
				}
			}
		}
		stmt1.close();
		closeConnection();
		return list;
	}// end method returnaccount
	/**
	 * Authenticates a user based on e-mail and password input.
	 * @param email
	 * The e-mail to match.
	 * @param password
	 * The password to match.
	 * @return
	 * True if the user was found and the password was correct, false if not.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public boolean authenticate(String email, char[] password)
			throws SQLException {
		openConnection();
		boolean returnvalue = false;
		String hashedpassword = PasswordHash.HashPassword(password);
		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT id FROM users WHERE email=? AND password=? AND DEACTIVATED=0");
		stmt.setString(1, email);
		stmt.setString(2, hashedpassword);
		ResultSet rs = stmt.executeQuery();

		if (rs.first()) {
			returnvalue = true;
		}
		stmt.close();
		closeConnection();

		return returnvalue;
	}// end method authenticate
	
	/**
	 * Checks if an e-mail exists in the database and is not disabled. Used to validate if a user can receive transactions.
	 * @param email
	 * The e-mail to check.
	 * @return
	 * True if the e-mail exists and is active, false if not.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public boolean checkEmail(String email) throws SQLException {
		openConnection();
		boolean returnvalue = false;

		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT id FROM users WHERE email=? AND DEACTIVATED=0 ");
		stmt.setString(1, email);
		ResultSet rs = stmt.executeQuery();

		if (rs.first()) {
			returnvalue = true;
		}

		stmt.close();
		closeConnection();
		return returnvalue;
	}
	/**
	 * Checks if an e-mail exists in the database. Used to validate if a user already exists or not, regardless if active.
	 * @param email
	 * The e-mail to check.
	 * @return
	 * True of the e-mail exists, false if not.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public boolean checkEmailAll(String email) throws SQLException {
		openConnection();
		boolean returnvalue = false;

		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT id FROM users WHERE email=?");
		stmt.setString(1, email);
		ResultSet rs = stmt.executeQuery();

		if (rs.first()) {
			returnvalue = true;
		}

		stmt.close();

		closeConnection();

		return returnvalue;
	}
	/**
	 * Sets the Exchange Rate to the parameter specified.
	 * @param rate
	 * The rate to set.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public void setExchangeRate(double rate) throws SQLException {
		openConnection();

		PreparedStatement stmt = sqlconnection
				.prepareStatement("UPDATE OPTIONS SET EXCHRATE=?");
		stmt.setDouble(1, rate);
		stmt.executeUpdate();
		stmt.close();
		sqlconnection.commit();
		closeConnection();

	}
	
	/**
	 * Gets the Exchange Rate.
	 * @return
	 * The rate set.
	 * @throws SQLException
	 * If any error occurs.
	 */
	public double getExchangeRate() throws SQLException {
		double returnrate = 0;
		openConnection();
		PreparedStatement stmt = sqlconnection
				.prepareStatement("SELECT EXCHRATE FROM OPTIONS");
		ResultSet rs = stmt.executeQuery();
		rs.first();
		returnrate = rs.getDouble("EXCHRATE");
		stmt.close();
		
		closeConnection();
		return returnrate;

	}

}
